package www.whqgdx.tangsenling.pingdingshan;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;

import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * @author：Jason
 * @date：2018/10/23 23:59
 * @email：jasonwan@lptiyu.com
 */

public class TextUtils {
    public static final int TYPE_HEIGHT = 1;
    public static final int TYPE_WEIGHT = 2;
    public static final int TYPE_LUNG = 3;
    public static final int TYPE_FIFTY = 4;
    public static final int TYPE_REACH = 5;
    public static final int TYPE_ROPE = 6;//一分钟跳绳
    public static final int TYPE_SIT_UP = 7;
    public static final int TYPE_SHUTTLER_RUN = 8;//50米x8往返跑
    public static final int TYPE_JUMP = 9;
    public static final int TYPE_PULL_UP = 10;
    public static final int TYPE_MIDDLE_RUN = 11;
    public static final int TYPE_Eye = 12;
    public static final int TYPE_CHUANJING = 13;
    public static final int TYPE_QUGUANGBUZHENG = 14;
    public static final DecimalFormat df = new DecimalFormat("#.0");

    public static boolean isEmpty(String s) {
        return s == null || s.trim().length() <= 0;
    }

    public static boolean isNotEmpty(String s) {
        return s != null && s.trim().length() > 0;
    }

    public static boolean equal(String s1, String s2) {
        return isNotEmpty(s1) && isNotEmpty(s2) && s1.equals(s2);
    }

    /**
     * 填充缺项的数据
     *
     * @param file               体测成绩表excel文件
     * @param isFillHeightWeight 是否填充身高体重
     */
    public static void fillDataToExcel(File file, boolean isFillHeightWeight, boolean
            isFillLung, boolean isFillFifty, boolean isFillJump, boolean isFillReach, boolean isFillEightHundred,
                                       boolean isFillOneThousand, boolean isFillSitUp, boolean isFillPullUp,
                                       boolean isFillEye) {
        WritableSheet sheet = null;
        Workbook workbook = null;
        WritableWorkbook writableWorkbook = null;
        try {
            InputStream fileInputStream = new FileInputStream(file);
            workbook = Workbook.getWorkbook(fileInputStream);
            writableWorkbook = Workbook.createWorkbook(file, workbook);
            sheet = writableWorkbook.getSheet(0);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
        if (sheet == null) {
            return;
        }
        //获取行数
        int sheetRows = sheet.getRows();
        for (int i = 1; i < sheetRows; i++) {
            int sex = 2;
            try {
                sex = Integer.parseInt(sheet.getCell(6, i).getContents());
            } catch (NumberFormatException e) {
                e.printStackTrace();
            }
            //填充身高体重
            if (isFillHeightWeight) {
                String height = sheet.getCell(9, i).getContents();
                String weight = sheet.getCell(10, i).getContents();
                if (isEmpty(height)) {
                    try {
                        sheet.addCell(new Label(9, i, randomDataForUnivesity(sex, TYPE_HEIGHT)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
                if (isEmpty(weight)) {
                    try {
                        sheet.addCell(new Label(10, i, randomDataForUnivesity(sex, TYPE_WEIGHT)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            if (isFillLung) {
                //填充肺活量
                String lung = sheet.getCell(11, i).getContents();
                if (isEmpty(lung)) {
                    try {
                        sheet.addCell(new Label(11, i, randomDataForUnivesity(sex, TYPE_LUNG)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            //填充50米
            if (isFillFifty) {
                String fifty = sheet.getCell(12, i).getContents();
                if (isEmpty(fifty)) {
                    try {
                        sheet.addCell(new Label(12, i, randomDataForUnivesity(sex, TYPE_FIFTY)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            if (isFillJump) {
                //填充立定跳远
                String jump = sheet.getCell(13, i).getContents();
                if (isEmpty(jump)) {
                    try {
                        sheet.addCell(new Label(13, i, randomDataForUnivesity(sex, TYPE_JUMP)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            if (isFillReach) {
                //填充坐位体前屈
                String reach = sheet.getCell(14, i).getContents();
                if (isEmpty(reach)) {
                    try {
                        sheet.addCell(new Label(14, i, randomDataForUnivesity(sex, TYPE_REACH)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            if (isFillEightHundred) {
                //填充800米跑
                String eightHundred = sheet.getCell(15, i).getContents();
                if (sex == 2 && isEmpty(eightHundred)) {
                    try {
                        sheet.addCell(new Label(15, i, randomDataForUnivesity(sex, TYPE_MIDDLE_RUN)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            if (isFillOneThousand) {
                //填充1000米跑
                String oneThousand = sheet.getCell(16, i).getContents();
                if (sex == 1 && isEmpty(oneThousand)) {
                    try {
                        sheet.addCell(new Label(16, i, randomDataForUnivesity(sex, TYPE_MIDDLE_RUN)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            if (isFillSitUp) {
                //填充仰卧起坐
                String situp = sheet.getCell(17, i).getContents();
                if (sex == 2 && isEmpty(situp)) {
                    try {
                        sheet.addCell(new Label(17, i, randomDataForUnivesity(sex, TYPE_SIT_UP)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            if (isFillPullUp) {
                //填充引体向上
                String pullup = sheet.getCell(18, i).getContents();
                if (sex == 1 && isEmpty(pullup)) {
                    try {
                        sheet.addCell(new Label(18, i, randomDataForUnivesity(sex, TYPE_PULL_UP)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            //填充视力数据
            if (isFillEye) {
                String leftEye = sheet.getCell(19, i).getContents();
                String rightEye = sheet.getCell(20, i).getContents();
                String leftChuanJing = sheet.getCell(23, i).getContents();
                String rightChuanJing = sheet.getCell(24, i).getContents();
                String eyeValue = randomDataForUnivesity(sex, TYPE_Eye);

                try {
                    if (isEmpty(leftEye)) {
                        sheet.addCell(new Label(19, i, eyeValue));
                        if (Double.parseDouble(eyeValue) >= 5.0d) {
                            sheet.addCell(new Label(23, i, "0"));
                        } else {
                            sheet.addCell(new Label(23, i, "9"));
                        }
                    } else {
                        if (Double.parseDouble(leftEye) >= 5.0d) {
                            sheet.addCell(new Label(23, i, "0"));
                        } else {
                            if (isEmpty(leftChuanJing)) {
                                sheet.addCell(new Label(23, i, "9"));
                            }
                        }
                    }
                    if (isEmpty(rightEye)) {
                        sheet.addCell(new Label(20, i, eyeValue));
                        if (Double.parseDouble(eyeValue) >= 5.0d) {
                            sheet.addCell(new Label(24, i, "0"));
                        } else {
                            sheet.addCell(new Label(24, i, "9"));
                        }
                    } else {
                        if (Double.parseDouble(rightEye) >= 5.0d) {
                            sheet.addCell(new Label(24, i, "0"));
                        } else {
                            if (isEmpty(rightChuanJing)) {
                                sheet.addCell(new Label(24, i, "9"));
                            }
                        }
                    }
                    String leftQuGuangBuZheng = sheet.getCell(21, i).getContents();
                    if (isEmpty(leftQuGuangBuZheng)) {
                        sheet.addCell(new Label(21, i, "9"));
                    }
                    String rightQuGuangBuZheng = sheet.getCell(22, i).getContents();
                    if (isEmpty(rightQuGuangBuZheng)) {
                        sheet.addCell(new Label(22, i, "9"));
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        try {
            writableWorkbook.write();
            workbook.close();
            writableWorkbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    /**
     * 填充小学模板缺项的数据
     *
     * @param file               体测成绩表excel文件
     * @param isFillHeightWeight 是否填充身高体重
     */
    public static void fillPrimaryDataToExcel(File file, boolean isFillHeightWeight, boolean
            isFillLung, boolean isFillFifty, boolean isFillReach, boolean isFillRope, boolean isFillEye, boolean
                                                      isFillSitUp, boolean isFillShuttleRun) {
        WritableSheet sheet = null;
        Workbook workbook = null;
        WritableWorkbook writableWorkbook = null;
        try {
            InputStream fileInputStream = new FileInputStream(file);
            workbook = Workbook.getWorkbook(fileInputStream);
            writableWorkbook = Workbook.createWorkbook(file, workbook);
            sheet = writableWorkbook.getSheet(0);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
        if (sheet == null) {
            return;
        }
        //获取行数
        int sheetRows = sheet.getRows();
        for (int i = 1; i < sheetRows; i++) {
            int grade = 11;
            try {
                grade = Integer.parseInt(sheet.getCell(0, i).getContents());
            } catch (NumberFormatException e) {
                e.printStackTrace();
            }
            //填充身高体重
            if (isFillHeightWeight) {
                String height = sheet.getCell(9, i).getContents();
                String weight = sheet.getCell(10, i).getContents();
                if (isEmpty(height)) {
                    try {
                        sheet.addCell(new Label(9, i, randomDataForPrimarySchool(grade, TYPE_HEIGHT)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
                if (isEmpty(weight)) {
                    try {
                        sheet.addCell(new Label(10, i, randomDataForPrimarySchool(grade, TYPE_WEIGHT)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            if (isFillLung) {
                //填充肺活量
                String lung = sheet.getCell(11, i).getContents();
                if (isEmpty(lung)) {
                    try {
                        sheet.addCell(new Label(11, i, randomDataForPrimarySchool(grade, TYPE_LUNG)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            //填充50米
            if (isFillFifty) {
                String fifty = sheet.getCell(12, i).getContents();
                if (isEmpty(fifty)) {
                    try {
                        sheet.addCell(new Label(12, i, randomDataForPrimarySchool(grade, TYPE_FIFTY)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            if (isFillReach) {
                //填充坐位体前屈
                String reach = sheet.getCell(13, i).getContents();
                if (isEmpty(reach)) {
                    try {
                        sheet.addCell(new Label(13, i, randomDataForPrimarySchool(grade, TYPE_REACH)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            if (isFillRope) {
                //填充一分钟跳绳
                String eightHundred = sheet.getCell(14, i).getContents();
                if (isEmpty(eightHundred)) {
                    try {
                        sheet.addCell(new Label(14, i, randomDataForPrimarySchool(grade, TYPE_ROPE)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            //填充视力数据
            if (isFillEye) {
                String leftEye = sheet.getCell(15, i).getContents();
                String rightEye = sheet.getCell(16, i).getContents();
                String leftChuanJing = sheet.getCell(19, i).getContents();
                String rightChuanJing = sheet.getCell(20, i).getContents();
                String eyeValue = randomDataForUnivesity(grade, TYPE_Eye);

                try {
                    if (isEmpty(leftEye)) {
                        sheet.addCell(new Label(15, i, eyeValue));
                        if (Double.parseDouble(eyeValue) >= 5.0d) {
                            sheet.addCell(new Label(19, i, "0"));
                        } else {
                            sheet.addCell(new Label(19, i, "9"));
                        }
                    } else {
                        if (Double.parseDouble(leftEye) >= 5.0d) {
                            sheet.addCell(new Label(19, i, "0"));
                        } else {
                            if (isEmpty(leftChuanJing)) {
                                sheet.addCell(new Label(19, i, "9"));
                            }
                        }
                    }
                    if (isEmpty(rightEye)) {
                        sheet.addCell(new Label(16, i, eyeValue));
                        if (Double.parseDouble(eyeValue) >= 5.0d) {
                            sheet.addCell(new Label(20, i, "0"));
                        } else {
                            sheet.addCell(new Label(20, i, "9"));
                        }
                    } else {
                        if (Double.parseDouble(rightEye) >= 5.0d) {
                            sheet.addCell(new Label(20, i, "0"));
                        } else {
                            if (isEmpty(rightChuanJing)) {
                                sheet.addCell(new Label(20, i, "9"));
                            }
                        }
                    }
                    String leftQuGuangBuZheng = sheet.getCell(17, i).getContents();
                    if (isEmpty(leftQuGuangBuZheng)) {
                        sheet.addCell(new Label(17, i, "9"));
                    }
                    String rightQuGuangBuZheng = sheet.getCell(18, i).getContents();
                    if (isEmpty(rightQuGuangBuZheng)) {
                        sheet.addCell(new Label(18, i, "9"));
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (isFillSitUp) {
                //填充仰卧起坐
                String situp = sheet.getCell(21, i).getContents();
                if (isEmpty(situp)) {
                    try {
                        sheet.addCell(new Label(21, i, randomDataForPrimarySchool(grade, TYPE_SIT_UP)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
            if (isFillShuttleRun) {
                //填充50米x8往返跑
                String pullup = sheet.getCell(22, i).getContents();
                if (isEmpty(pullup)) {
                    try {
                        sheet.addCell(new Label(22, i, randomDataForPrimarySchool(grade, TYPE_SHUTTLER_RUN)));
                    } catch (WriteException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        try {
            writableWorkbook.write();
            workbook.close();
            writableWorkbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    /**
     * 上调合格率
     *
     * @param file
     * @param isModifyLung
     * @param isModifyFifty
     * @param isModifyJump
     * @param isModifyReach
     * @param isModifyEightHundred
     * @param isModifyOneThousand
     * @param isModifySitUp
     * @param isModifyPullUp
     */

    public static void increaseRate(File file, boolean isModifyLung, boolean isModifyFifty,
                                    boolean isModifyJump, boolean isModifyReach, boolean isModifyEightHundred,
                                    boolean isModifyOneThousand, boolean isModifySitUp, boolean isModifyPullUp) {
        WritableSheet sheet = null;
        Workbook workbook = null;
        WritableWorkbook writableWorkbook = null;
        try {
            InputStream fileInputStream = new FileInputStream(file);
            workbook = Workbook.getWorkbook(fileInputStream);
            writableWorkbook = Workbook.createWorkbook(file, workbook);
            sheet = writableWorkbook.getSheet(0);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
        if (sheet == null) {
            return;
        }
        //获取行数
        int sheetRows = sheet.getRows();
        for (int i = 1; i < sheetRows; i++) {
            int sex = Integer.parseInt(sheet.getCell(6, i).getContents());
            //修改肺活量
            if (isModifyLung) {
                String lung = sheet.getCell(11, i).getContents();
                try {
                    if (isNotEmpty(lung)) {
                        int abs = Integer.parseInt(lung);
                        if ((sex == 1 && abs < 3200) || (sex == 2 && abs < 2050)) {
                            sheet.addCell(new Label(11, i, String.valueOf(abs + 100)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            if (isModifyFifty) {
                //修改50米
                String fifty = sheet.getCell(12, i).getContents();
                try {
                    if (isNotEmpty(fifty)) {
                        float abs = Float.parseFloat(fifty);
                        if ((sex == 1 && abs > 9f) || (sex == 2 && abs > 10.2f)) {
                            sheet.addCell(new Label(12, i, String.valueOf(df.format(abs - 0.1f))));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改立定跳远
            if (isModifyJump) {
                String jump = sheet.getCell(13, i).getContents();
                try {
                    if (isNotEmpty(jump)) {
                        float abs = Float.parseFloat(jump);
                        if ((sex == 1 && abs < 210f) || (sex == 2 && abs < 152f)) {
                            sheet.addCell(new Label(13, i, String.valueOf(df.format(abs + 5.0f))));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改体前屈
            if (isModifyReach) {
                String reach = sheet.getCell(14, i).getContents();
                try {
                    if (isNotEmpty(reach)) {
                        float abs = Float.parseFloat(reach);
                        if ((sex == 1 && abs < 4.2f) || (sex == 2 && abs < 6.5f)) {
                            sheet.addCell(new Label(14, i, String.valueOf(df.format(abs + 1.5f))));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改800米
            if (isModifyEightHundred) {
                String eightHundred = sheet.getCell(15, i).getContents();
                try {
                    if (sex == 1 && isNotEmpty(eightHundred)) {
                        int abs = minSecToSecond(eightHundred);
                        if (abs > 270) {
                            sheet.addCell(new Label(15, i, secondToMinSec(abs - 5)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改1000米
            if (isModifyOneThousand) {
                String oneThounsand = sheet.getCell(16, i).getContents();
                try {
                    if (sex == 2 && isNotEmpty(oneThounsand)) {
                        int abs = minSecToSecond(oneThounsand);
                        if (abs > 272) {
                            sheet.addCell(new Label(16, i, secondToMinSec(abs - 5)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改仰卧起坐
            if (isModifySitUp) {
                String rope = sheet.getCell(17, i).getContents();
                try {
                    if (isNotEmpty(rope)) {
                        int abs = Integer.parseInt(rope);
                        if (sex == 2 && abs < 27) {
                            sheet.addCell(new Label(17, i, String.valueOf(abs + 2)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改引体向上
            if (isModifyPullUp) {
                String sitUp = sheet.getCell(18, i).getContents();
                try {
                    if (isNotEmpty(sitUp)) {
                        int abs = Integer.parseInt(sitUp);
                        if (sex == 1 && abs < 11) {
                            sheet.addCell(new Label(18, i, String.valueOf(abs + 1)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
        }
        try {
            writableWorkbook.write();
            workbook.close();
            writableWorkbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    /**
     * 下调合格率
     *
     * @param file
     * @param isModifyLung
     * @param isModifyFifty
     * @param isModifyJump
     * @param isModifyReach
     * @param isModifyEightHundred
     * @param isModifyOneThousand
     * @param isModifySitUp
     * @param isModifyPullUp
     */
    public static void decreaseRate(File file, boolean isModifyLung, boolean isModifyFifty,
                                    boolean isModifyJump, boolean isModifyReach, boolean isModifyEightHundred,
                                    boolean isModifyOneThousand, boolean isModifySitUp, boolean isModifyPullUp) {
        WritableSheet sheet = null;
        Workbook workbook = null;
        WritableWorkbook writableWorkbook = null;
        try {
            InputStream fileInputStream = new FileInputStream(file);
            workbook = Workbook.getWorkbook(fileInputStream);
            writableWorkbook = Workbook.createWorkbook(file, workbook);
            sheet = writableWorkbook.getSheet(0);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
        if (sheet == null) {
            return;
        }
        //获取行数
        int sheetRows = sheet.getRows();
        for (int i = 1; i < sheetRows; i++) {
            int sex = Integer.parseInt(sheet.getCell(6, i).getContents());
            //修改肺活量
            if (isModifyLung) {
                String lung = sheet.getCell(11, i).getContents();
                try {
                    if (isNotEmpty(lung)) {
                        int abs = Integer.parseInt(lung);
                        if ((sex == 1 && abs > 3200) || (sex == 2 && abs > 2050)) {
                            sheet.addCell(new Label(11, i, String.valueOf(abs - 100)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            if (isModifyFifty) {
                //修改50米
                String fifty = sheet.getCell(12, i).getContents();
                try {
                    if (isNotEmpty(fifty)) {
                        float abs = Float.parseFloat(fifty);
                        if ((sex == 1 && abs < 9f) || (sex == 2 && abs < 10.2f)) {
                            sheet.addCell(new Label(12, i, String.valueOf(df.format(abs + 0.1f))));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改立定跳远
            if (isModifyJump) {
                String jump = sheet.getCell(13, i).getContents();
                try {
                    if (isNotEmpty(jump)) {
                        float abs = Float.parseFloat(jump);
                        if ((sex == 1 && abs > 210f) || (sex == 2 && abs > 152f)) {
                            sheet.addCell(new Label(13, i, String.valueOf(df.format(abs - 5.0f))));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改体前屈
            if (isModifyReach) {
                String reach = sheet.getCell(14, i).getContents();
                try {
                    if (isNotEmpty(reach)) {
                        float abs = Float.parseFloat(reach);
                        if ((sex == 1 && abs > 4.2f) || (sex == 2 && abs > 6.5f)) {
                            sheet.addCell(new Label(14, i, String.valueOf(df.format(abs - 1.5f))));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改800米
            if (isModifyEightHundred) {
                String eightHundred = sheet.getCell(15, i).getContents();
                try {
                    if (sex == 1 && isNotEmpty(eightHundred)) {
                        int abs = minSecToSecond(eightHundred);
                        if (abs < 270) {
                            sheet.addCell(new Label(15, i, secondToMinSec(abs + 5)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改1000米
            if (isModifyOneThousand) {
                String oneThounsand = sheet.getCell(16, i).getContents();
                try {
                    if (sex == 2 && isNotEmpty(oneThounsand)) {
                        int abs = minSecToSecond(oneThounsand);
                        if (abs < 272) {
                            sheet.addCell(new Label(16, i, secondToMinSec(abs + 5)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改仰卧起坐
            if (isModifySitUp) {
                String rope = sheet.getCell(17, i).getContents();
                try {
                    if (isNotEmpty(rope)) {
                        int abs = Integer.parseInt(rope);
                        if (sex == 2 && abs > 27) {
                            sheet.addCell(new Label(17, i, String.valueOf(abs - 2)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改引体向上
            if (isModifyPullUp) {
                String sitUp = sheet.getCell(18, i).getContents();
                try {
                    if (isNotEmpty(sitUp)) {
                        int abs = Integer.parseInt(sitUp);
                        if (sex == 1 && abs > 11) {
                            sheet.addCell(new Label(18, i, String.valueOf(abs - 1)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
        }
        try {
            writableWorkbook.write();
            workbook.close();
            writableWorkbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    /**
     * 上调良好率
     *
     * @param file
     * @param isModifyLung
     * @param isModifyFifty
     * @param isModifyJump
     * @param isModifyReach
     * @param isModifyEightHundred
     * @param isModifyOneThousand
     * @param isModifySitUp
     * @param isModifyPullUp
     */
    public static void increaseWellRate(File file, boolean isModifyLung, boolean isModifyFifty, boolean
            isModifyJump, boolean isModifyReach, boolean isModifyEightHundred, boolean isModifyOneThousand,
                                        boolean isModifySitUp, boolean isModifyPullUp) {
        WritableSheet sheet = null;
        Workbook workbook = null;
        WritableWorkbook writableWorkbook = null;
        try {
            InputStream fileInputStream = new FileInputStream(file);
            workbook = Workbook.getWorkbook(fileInputStream);
            writableWorkbook = Workbook.createWorkbook(file, workbook);
            sheet = writableWorkbook.getSheet(0);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
        if (sheet == null) {
            return;
        }
        //获取行数
        int sheetRows = sheet.getRows();
        for (int i = 1; i < sheetRows; i++) {
            int sex = Integer.parseInt(sheet.getCell(6, i).getContents());
            //修改肺活量
            if (isModifyLung) {
                String lung = sheet.getCell(11, i).getContents();
                try {
                    if (isNotEmpty(lung)) {
                        int abs = Integer.parseInt(lung);
                        if ((sex == 1 && abs > 3200 && abs < 4900) || (sex == 2 && abs > 2050 && abs < 3350)) {
                            sheet.addCell(new Label(11, i, String.valueOf(abs + 100)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            if (isModifyFifty) {
                //修改50米
                String fifty = sheet.getCell(12, i).getContents();
                try {
                    if (isNotEmpty(fifty)) {
                        float abs = Float.parseFloat(fifty);
                        if ((sex == 1 && abs > 6.8f && abs < 9f) || (sex == 2 && abs > 7.6f && abs < 10.2f)) {
                            sheet.addCell(new Label(12, i, String.valueOf(df.format(abs - 0.1f))));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改立定跳远
            if (isModifyJump) {
                String jump = sheet.getCell(13, i).getContents();
                try {
                    if (isNotEmpty(jump)) {
                        float abs = Float.parseFloat(jump);
                        if ((sex == 1 && abs > 210f && abs < 265f) || (sex == 2 && abs > 152f && abs < 196f)) {
                            sheet.addCell(new Label(13, i, String.valueOf(df.format(abs + 5.0f))));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改体前屈
            if (isModifyReach) {
                String reach = sheet.getCell(14, i).getContents();
                try {
                    if (isNotEmpty(reach)) {
                        float abs = Float.parseFloat(reach);
                        if ((sex == 1 && abs > 4.2f && abs < 21.5f) || (sex == 2 && abs > 6.5f && abs < 22.4f)) {
                            sheet.addCell(new Label(14, i, String.valueOf(df.format(abs + 1.5f))));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改800米
            if (isModifyEightHundred) {
                String eightHundred = sheet.getCell(15, i).getContents();
                try {
                    if (sex == 1 && isNotEmpty(eightHundred)) {
                        int abs = minSecToSecond(eightHundred);
                        if (abs > 208 && abs < 270) {
                            sheet.addCell(new Label(15, i, secondToMinSec(abs - 5)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改1000米
            if (isModifyOneThousand) {
                String oneThounsand = sheet.getCell(16, i).getContents();
                try {
                    if (sex == 2 && isNotEmpty(oneThounsand)) {
                        int abs = minSecToSecond(oneThounsand);
                        if (abs > 205 && abs < 272) {
                            sheet.addCell(new Label(16, i, secondToMinSec(abs - 5)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改仰卧起坐
            if (isModifySitUp) {
                String rope = sheet.getCell(17, i).getContents();
                try {
                    if (isNotEmpty(rope)) {
                        int abs = Integer.parseInt(rope);
                        if (sex == 2 && abs > 27 && abs < 53) {
                            sheet.addCell(new Label(17, i, String.valueOf(abs + 2)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            //修改引体向上
            if (isModifyPullUp) {
                String sitUp = sheet.getCell(18, i).getContents();
                try {
                    if (isNotEmpty(sitUp)) {
                        int abs = Integer.parseInt(sitUp);
                        if (sex == 1 && abs > 11 && abs < 18) {
                            sheet.addCell(new Label(18, i, String.valueOf(abs + 1)));
                        }
                    }
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
        }
        try {
            writableWorkbook.write();
            workbook.close();
            writableWorkbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    /**
     * 能取到min，但取不到max
     *
     * @param min
     * @param max
     * @return
     */
    public static String randomDouble(double min, double max) {
        if (max < min) {
            return "0.0";
        }
        return df.format(Math.random() * (max - min) + min);
    }

    public static String randomInt(int min, int max) {
        if (max < min) {
            return "0";
        }
        return String.valueOf((int) (Math.random() * (max - min)) + min);
    }

    public static String randomDataForUnivesity(int sex, int type) {
        if (type == TYPE_HEIGHT) {
            if (sex == 1) {
                //男生
                return randomDouble(160, 180);
            } else {
                //女生
                return randomDouble(155, 175);
            }
        } else if (type == TYPE_WEIGHT) {
            if (sex == 1) {
                //男生
                return randomDouble(50, 70);
            } else {
                //女生
                return randomDouble(40, 60);
            }
        } else if (type == TYPE_LUNG) {
            if (sex == 1) {
                //男生
                return randomInt(3100, 4180);
            } else {
                //女生
                return randomInt(2000, 2900);
            }
        } else if (type == TYPE_FIFTY) {
            if (sex == 1) {
                return randomDouble(7.3d, 9.1d);
            } else {
                return randomDouble(8.5d, 10.3d);
            }
        } else if (type == TYPE_REACH) {
            if (sex == 1) {
                return randomDouble(3.7d, 16.3d);
            } else {
                return randomDouble(6d, 17.7d);
            }
        } else if (type == TYPE_SIT_UP) {
            if (sex == 1) {
                return "-1";
            } else {
                return randomInt(26, 44);
            }
        } else if (type == TYPE_JUMP) {
            if (sex == 1) {
                return randomInt(208, 244);
            } else {
                return randomInt(151, 178);
            }
        } else if (type == TYPE_PULL_UP) {
            if (sex == 1) {
                return randomInt(5, 10);
            } else {
                return "-1";
            }
        } else if (type == TYPE_MIDDLE_RUN) {
            String s;
            if (sex == 1) {
                s = randomInt(227, 272);
            } else {
                s = randomInt(224, 274);
            }
            return secondToMinSec(s);
        } else if (type == TYPE_Eye) {
            return randomDouble(3.0d, 5.3d);
        } else {
            return "";
        }
    }

    public static String secondToMinSec(int seconds) {
        return secondToMinSec(seconds + "");
    }

    public static String secondToMinSec(String seconds) {
        int i = Integer.parseInt(seconds);
        int j = i % 60;
        if (j >= 10) {
            return i / 60 + "'" + j + "''";
        } else {
            return i / 60 + "'0" + j + "''";
        }
    }

    public static int minSecToSecond(String minSec) {
        String[] split = minSec.split("'");
        return Integer.parseInt(split[0]) * 60 + Integer.parseInt(split[1]);
    }

    public static String randomDataForPrimarySchool(int grade, int type) {
        if (type == TYPE_HEIGHT) {
            if (grade == 11) {
                //一年级
                return randomDouble(120, 130);
            } else if (grade == 12) {
                return randomDouble(120, 135);
            } else if (grade == 13) {
                return randomDouble(130, 140);
            } else if (grade == 14) {
                return randomDouble(130, 145);
            } else if (grade == 15) {
                return randomDouble(140, 150);
            } else if (grade == 16) {
                return randomDouble(140, 155);
            } else {
                return randomDouble(130, 145);
            }
        } else if (type == TYPE_WEIGHT) {
            if (grade == 11) {
                //一年级
                return randomDouble(20, 30);
            } else if (grade == 12) {
                return randomDouble(20, 30);
            } else if (grade == 13) {
                return randomDouble(25, 35);
            } else if (grade == 14) {
                return randomDouble(30, 40);
            } else if (grade == 15) {
                return randomDouble(30, 45);
            } else if (grade == 16) {
                return randomDouble(30, 45);
            } else {
                return randomDouble(130, 145);
            }
        } else if (type == TYPE_LUNG) {
            if (grade == 11) {
                //一年级
                return randomInt(555, 1200);
            } else if (grade == 12) {
                return randomInt(666, 1500);
            } else if (grade == 13) {
                return randomInt(777, 1800);
            } else if (grade == 14) {
                return randomInt(888, 2000);
            } else if (grade == 15) {
                return randomInt(888, 2200);
            } else if (grade == 16) {
                return randomInt(888, 2400);
            } else {
                return randomInt(888, 2400);
            }
        } else if (type == TYPE_FIFTY) {
            if (grade == 11 || grade == 12 || grade == 13) {
                return randomDouble(10d, 14d);
            } else if (grade == 14 || grade == 15 || grade == 16) {
                return randomDouble(9d, 13d);
            } else {
                return randomDouble(9d, 13d);
            }
        } else if (type == TYPE_REACH) {
            return randomDouble(0.1d, 15d);
        } else if (type == TYPE_ROPE) {
            if (grade == 11) {
                return randomInt(5, 50);
            } else if (grade == 12) {
                return randomInt(10, 60);
            } else if (grade == 13) {
                return randomInt(20, 70);
            } else if (grade == 14) {
                return randomInt(25, 80);
            } else if (grade == 15) {
                return randomInt(30, 90);
            } else if (grade == 16) {
                return randomInt(40, 110);
            } else {
                return randomInt(40, 110);
            }
        } else if (type == TYPE_SIT_UP) {
            if (grade < 13) {
                return "";
            } else if (grade == 13) {
                return randomInt(10, 25);
            } else if (grade == 14) {
                return randomInt(10, 30);
            } else if (grade == 15) {
                return randomInt(15, 35);
            } else if (grade == 16) {
                return randomInt(15, 40);
            } else {
                return randomInt(15, 40);
            }
        } else if (type == TYPE_SHUTTLER_RUN) {
            if (grade == 15 || grade == 16) {
                return secondToMinSec(randomInt(105, 132));
            } else {
                return "";
            }
        } else {
            return "";
        }
    }
}
